package com.bringway.p2p.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.JkxxDao;

/**
 * 
 * @author hc
 * @version 创建时间：2016年2月2日 上午10:22:16
 * @功能：借款信息
 */

@Repository
public  class JkxxDaoImpl  extends P2PJdbcDao implements JkxxDao{
	
	Logger logger = Logger.getLogger(JkxxDaoImpl.class);


	public List queryJkxxList(JSONObject params) {

		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String jkqx = params.optString("jkqx");
		String jkbt = params.optString("jkbt");
		String username = params.optString("username");
		String sjhm = params.optString("sjhm");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("USERNAME", username);
		paramMap.put("jkqx", jkqx);
		paramMap.put("jkbt", jkbt);
		paramMap.put("sjhm", sjhm);
		
		sql.append(" select t.id as ID,t.jkje as JKJE,t.jkqx as JKQX,t.yhke as YHKE,t.qcfwf as QCFWF,t.jkyt as JKYT,t.jkbt as JKBT,t.jkms as JKMS,t.username as USERNAME,t.sqrq as SQRQ,t1.sjhm as SJHM  ");
		sql.append("  from v_p2p_jkxx t left join v_p2p_user_tjr t1 ");
		sql.append(" on t.USERNAME = t1.USER_NAME where 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND SQRQ >= :startDate");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND SQRQ <= :endDate");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(jkqx)){
			sql.append(" AND JKQX like "+ "'%"+jkqx+"%'");
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM like "+ "'%"+sjhm+"%'");
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND USERNAME like "+ "'%"+username+"%'");
		}
		if(StringUtils.isNotEmpty(jkbt)){
			sql.append(" AND JKBT like "+ "'%"+jkbt+"%'");
		}
		sql.append(" order by SQRQ desc LIMIT :START,:LIMIT ");
		System.out.println(sql.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}

	@SuppressWarnings("deprecation")
	@Override
	public int getJkxxCount(JSONObject params) {
		
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String jkqx = params.optString("jkqx");
		String username=params.optString("username");
		String jkbt=params.optString("jkbt");
		String sjhm=params.optString("sjhm");
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("USERNAME", username);
		paramMap.put("JKQX", jkqx);
		paramMap.put("JKBT", jkbt);
		paramMap.put("SJHM", sjhm);
		paramMap.put("startDate", startDate);
		paramMap.put("endDate", endDate);
		
		sql.append(" SELECT COUNT(*) CT ");
		sql.append(" FROM v_p2p_jkxx t left join v_p2p_user_tjr t1 ");
		sql.append(" on t.USERNAME = t1.USER_NAME  where 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND SQRQ >= :startDate");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND SQRQ <= :endDate");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(jkqx)){
			sql.append(" AND JKQX like "+ "'%"+jkqx+"%'");
		}
		
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND USERNAME like "+ "'%"+username+"%'");
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM like "+ "'%"+sjhm+"%'");
		}
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}


	@Override
	public String onAddJkxx(JSONObject params) {
		// TODO Auto-generated method stub
        StringBuffer sql = new StringBuffer();
		KeyHolder keyHolder = new GeneratedKeyHolder();
		String jkje = params.optString("jkje");
		String jkqx = params.optString("jkqx");
		String yhke = params.optString("yhke");
		String qcfwf = params.optString("qcfwf");
		String jkyt = params.optString("jkyt");
		String jkbt = params.optString("jkbt");
		String jkms = params.optString("jkms");
		String username = params.optString("username");
		String sqrq = params.optString("sqrq");
		
		String uuid = this.getUUId();
		sql.append(" INSERT INTO t_zhfw_jkxx(ID,JKJE,JKQX,YHKE");
		sql.append(" ,QCFWF,JKYT,JKBT,JKMS,USERNAME,SQRQ) ");
		sql.append(" VALUES (:UUID,:JKJE,:JKQX,:YHKE,:QCFWF");
		sql.append(" ,:JKYT,:JKBT,:JKMS,:USERNAME,:SQRQ) ");
		SqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("UUID", uuid)
				.addValue("JKJE", jkje)
                .addValue("JKQX", jkqx)
                .addValue("YHKE", yhke)
                .addValue("QCFWF", qcfwf)
                .addValue("JKYT", jkyt)
                .addValue("JKBT", jkbt)
                .addValue("JKMS", jkms)
                .addValue("USERNAME", username)
                .addValue("SQRQ", sqrq);
		try{
			logger.info("onAddJkxx sql is : " + sql.toString());
			logger.info("onAddJkxx parameters is : " + parameters);
			this.getNamedParameterJdbcTemplate().update(sql.toString(), parameters);
		}catch(Exception e){
			e.printStackTrace();
			logger.error("onAddJkxx err : " +e.getMessage());
		}
		return uuid;
	}
	
	
}
